MariaDB Database Configuration Lab

1. Install MariaDB

In this lab, you install a MariaDB database server.

You were asked to install a MariaDB database server on your server1.example.com machine. You need to secure the MariaDB service and configure it to accept connections only from local clients.

  1. Reset your server1.example.com machine.

  2. Install the mariadb and mariadb-client groups.

    [root@server1 ~]# yum groupinstall mariadb mariadb-client -y

  3. Start and enable the mariadb service.

    [root@server1 ~]# systemctl start mariadb
    [root@server1 ~]# systemctl enable mariadb

  4. Verify that MariaDB is listening on all interfaces.

    [root@server1 ~]# ss -tulpn | grep mysql

tcp  LISTEN 0  50  *:3306    *:*  users:"mysqld", 13611,13

    Why grep on mysql when you are running MariaDB?

    MariaDB is a fork of MySQL which is meant to act as a drop-in replacement.

  5. Enable the skip-networking directive.

    1. Open /etc/my.cnf in a text editor, and in section [mysqld], add the line:

      skip-networking=1

  6. Restart the mariadb service.

    [root@server1 ~]# systemctl restart mariadb

  7. Verify that MariaDB is not listening on all interfaces.

    [root@server1 ~]# ss -tulpn | grep mysql

    • This command should now return nothing.

  8. Secure the Mariadb service using the mysql_secure_installation tool. Set the root password to redhat, and answer Yes to all other questions.

    [root@server1 ~]# mysql_secure_installation

In order to log in to MariaDB to secure it, you need the current password for the root user.  If you just installed MariaDB, and you have not set the root password yet, the password is blank,
so you should just press Enter here.

Enter current password for root (Enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log in to the MariaDB root user without the proper authorization.

Set root password? [Y/n] Y
New password: redhat
Re-enter new password: redhat
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log in to MariaDB without creating a user account  for them.  This is intended only for testing, and to make the installation go a bit smoother.  You should remove them before moving into a production environment.

Remove anonymous users? [Y/n] Y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables ensures that all changes made so far take effect immediately.

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you completed all of the above steps, your MariaDB installation should now be secure.

Thanks for using MariaDB!

    1. Verify that the root user cannot log in without a password.

      [root@server1 ~]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

    2. Verify that the test database is removed.

      [root@server1 ~]# mysql -u root -p
Enter password: redhat
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> exit;
Bye

2. Manage Database Users

In this lab, you install a MariaDB server.

You were asked to create two MariaDB users on your server1.example.com machine, according to the following requirements:

User

Accepts Connection From Host

Password

Privileges

john

localhost

john_password

Insert, update, delete, and select on all tables from inventory database

steve

any host

steve_password

Select on all tables from inventory database

  1. Do not reset your server1.example.com system.

  2. Log in to and set up your server1.example.com system as root.

  3. Log in to and set up your desktop1.example.com system as root.

  4. Set up MariaDB with user root.

    1. In /etc/my.cnf, comment out skip-networking=1 and then restart the service.

      [root@server1 ~]# vi /etc/my.cnf
[root@server1 ~]# systemctl restart mariadb

    2. Open the firewall:

      [root@server1 ~]# firewall-cmd --permanent --add-service=mysql
[root@server1 ~]# firewall-cmd --reload

    3. Connect to MariaDB and create a database:

      [root@server1 ~]# mysql -u root -p
Password: redhat
MariaDB [(none)]> CREATE DATABASE inventory;
USE inventory;
CREATE TABLE category (
  id int(11) DEFAULT NULL,
  name varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOCK TABLES category WRITE;
INSERT INTO category VALUES (1,'Networking'),(2,'Servers'),(3,'Ssd');
UNLOCK TABLES;

  5. Create the users john and steve.

    • Note the passwords are the value of "identified by".

      MariaDB [(none)]> CREATE USER john@localhost identified by 'john_password';
MariaDB [(none)]> CREATE USER steve@% identified by 'steve_password';

  6. Grant insert, update, delete, and select privileges to user john.

    MariaDB [(none)]> GRANT INSERT, UPDATE, DELETE, SELECT on inventory.* to john@localhost;

  7. Grant select privilege to user steve.

    MariaDB [(none)]> GRANT SELECT on inventory.* to steve@%;

  8. Flush the privileges.

    MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit;

  9. Connect with user john and verify his privileges.

    1. Connect to MariaDB.

      [root@server1 ~]# mysql -u john -p

    2. Select the inventory database.

      MariaDB [(none)]> USE inventory;

    3. Verify the select privilege.

      MariaDB [(inventory)]> SELECT * FROM category;
+----+------------+
| id | name       |
+----+------------+
|  1 | Networking |
|  2 | Servers    |
|  3 | Ssd        |
+----+------------+
3 rows in set (0.00 sec)

    4. Verify the insert privilege.

      MariaDB [(inventory)]> INSERT INTO category(name) VALUES('Memory');
Query OK, 1 row affected (0.00 sec)

    5. Verify the update privilege.

      MariaDB [(inventory)]> UPDATE category SET name='Solid State Drive' where id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

    6. Verify the delete privilege.

      MariaDB [(inventory)]> DELETE FROM category WHERE name LIKE 'Memory';
Query OK, 1 row affected (0.01 sec)

  10. On your desktop1.example.com system, connect with user steve and verify his privileges.

    1. Make sure the MariaDB client is installed.

      [root@desktop1 ~]# yum -y install mariadb

    2. Connect to MariaDB.

      [root@desktop1 ~]# mysql -u steve -h server1.example.com -p

    3. Select the inventory database.

      MariaDB [(none)]> USE inventory;

    4. Verify the select privilege.

      MariaDB [(inventory)]> SELECT * FROM category;
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Networking       |
|  2 | Servers          |
|  3 | Sold State Drive |
+----+------------------+
3 rows in set (0.00 sec)

    5. Verify the insert privilege.

      MariaDB [(inventory)]> INSERT INTO category(name) VALUES('Memory');
ERROR 1142 (42000): INSERT command denied to user 'steve'@'desktop1.example.com' for table 'category'

3. Restore a MariaDB Database From Backup

In this lab, you restore a database from a MariaDB logical backup.

  1. Do not reset your server1.example.com system.

  2. Log in to and set up your server1.example.com system as root.

  3. Backup, destroy, and then restore from a logical backup.

    1. Backup the inventory database to a file:

      [root@server1 ~]# mysqldump -p inventory > /root/inventory.dump

    2. Connect to MariaDB as user root and drop the old database.

      [root@server1 ~]# mysql -u root -p
MariaDB [(none)]> drop database inventory;
MariaDB [(none)]> use inventory;
ERROR 1049 (42000): Unknown database 'inventory'

    3. Create a new database called inventory.

      MariaDB [(none)]> create database inventory;
MariaDB [(none)]> exit;

    4. Restore from the logical backup.

      [root@server1 ~]# mysql -u root -p inventory < /root/inventory.dump

  4. Verify restored data.

    1. Connect again to MariaDB.

      [root@server1 ~]# mysql -u root -p

    2. Connect to the inventory database.

      MariaDB [(none)]> use inventory;

    3. Select all categories.

      MariaDB [(inventory)]> SELECT * FROM category;
+----+-------------------+
| id | name              |
+----+-------------------+
|  1 | Networking        |
|  2 | Servers           |
|  3 | Solid State Drive |
+----+-------------------+
3 rows in set (0.00 sec)